/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package merlion.scm.icm.session;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.common.util.Consts;
import merlion.crm.sopm.entity.ItemLine;
import merlion.mrp.pmm.entity.FinishedGood;
import merlion.mrp.ppm.entity.Product;
import merlion.scm.icm.entity.InventoryReservation;
import merlion.scm.icm.entity.ProductInventoryRecord;
import merlion.scm.icm.entity.WarehouseLocation;
import merlion.scm.lcm.entity.DeliveryOrder;
import merlion.scm.rmpm.entity.GoodsReceipt;
import merlion.scm.rmpm.entity.RawMaterial;
import merlion.scm.rmpm.entity.RawMaterialInventoryBatch;
import merlion.scm.rmpm.entity.RawMaterialInventoryRecord;
import merlion.scm.rmpm.entity.RawMaterialItemLine;
import merlion.scm.rmpm.entity.RawMaterialSalesOrder;

/**
 *
 * @author Kenn & Yuwei
 */
@Stateless
public class InventoryControlSessionBean implements InventoryControlSessionBeanLocal{

    @PersistenceContext
    private EntityManager entityManager;

    public InventoryControlSessionBean() {
    }

    @Override
    public Collection<ProductInventoryRecord> getAllProductInventoryRecord() {
        return entityManager.createQuery("SELECT i from ProductInventoryRecord i").getResultList();
    }

    @Override
    public ProductInventoryRecord getProductInventoryRecordById(Long id) {
        return (ProductInventoryRecord) entityManager.createQuery("SELECT i from ProductInventoryRecord i WHERE i.id = " + id).getResultList().get(0);
    }

    @Override
    public ProductInventoryRecord getProductInventoryRecordByProduct(Product p) {
        return (ProductInventoryRecord) entityManager.createQuery("SELECT i from ProductInventoryRecord i WHERE i.product.id = " + p.getId()).getResultList().get(0);
    }

//    @Override
//    public Collection<FinishedGoodInventoryBatch> getFinishedGoodInventoryBatchByProductInventoryRecord(ProductInventoryRecord pir) {
//        Query q = entityManager.createQuery("SELECT b FROM ProductInventoryRecord p INNER JOIN p.batches b WHERE p.id = :p1 ORDER BY b.dateExpired DESC");
//        q.setParameter("p1", pir.getId());
//        Collection<FinishedGoodInventoryBatch> batches = q.getResultList();
//        return batches;
//    }
//
//    @Override
//    public FinishedGoodInventoryBatch getFinishedGoodInventoryBatchById(Long id) {
//        return (FinishedGoodInventoryBatch) entityManager.createQuery("SELECT f from FinishedGoodInventoryBatch f WHERE f.id = " + id).getResultList().get(0);
//    }
    @Override
    public ProductInventoryRecord saveProductInventoryRecord(ProductInventoryRecord pir) {
        return entityManager.merge(pir);
    }

    @Override
    public Collection<RawMaterialInventoryBatch> getRawMaterialInventoryBatchByRawMaterialInventoryRecord(RawMaterialInventoryRecord rm) {
        Query q = entityManager.createQuery("SELECT b FROM RawMaterialInventoryRecord r INNER JOIN r.batches b WHERE r.id = :p1 ORDER BY b.dateExpired DESC");
        q.setParameter("p1", rm.getId());
        Collection<RawMaterialInventoryBatch> batches = q.getResultList();
        return batches;
    }

    @Override
    public Collection<RawMaterialInventoryRecord> getAllRawMaterialInventoryRecord() {
        return entityManager.createQuery("SELECT r from RawMaterialInventoryRecord r").getResultList();
    }

    @Override
    public RawMaterialInventoryRecord getRawMaterialInventoryRecordById(Long id) {
        return (RawMaterialInventoryRecord) entityManager.createQuery("SELECT r from RawMaterialInventoryRecord r WHERE r.id = " + id).getResultList().get(0);
    }

    @Override
    public RawMaterialInventoryRecord getRawMaterialInventoryRecordByRawMaterial(RawMaterial rm) {

        Query q = entityManager.createQuery("SELECT r from RawMaterialInventoryRecord r WHERE r.rawMaterial.id =:p1");
        q.setParameter("p1", rm.getId());
        return (RawMaterialInventoryRecord) q.getResultList().get(0);
    }

    @Override
    public RawMaterialInventoryBatch getRawMaterialInventoryBatchById(Long id) {
        return (RawMaterialInventoryBatch) entityManager.createQuery("SELECT b from RawMaterialInventoryBatch b WHERE b.id = " + id).getResultList().get(0);
    }

    @Override
    public RawMaterialInventoryRecord saveRawMaterialInventoryRecord(RawMaterialInventoryRecord rmir) {
        return entityManager.merge(rmir);
    }

    @Override
    public Boolean reserveInventory(ItemLine deliveryOrderIl, Long qty) {
        Long toReserveQty = qty;

        System.out.println("RESERVE INVENTORY CALLED for deliveryOrder itemline id: " + deliveryOrderIl.getId());

        //entityManager.flush();
//        Query query2=entityManager.createQuery("SELECT i FROM Itemline i WHERE i.id=:p2");
//        query2.setParameter("p2", deliveryOrderIl.getId());
//        deliveryOrderIl = (ItemLine) query2.getSingleResult();
        
        deliveryOrderIl = entityManager.find(ItemLine.class, deliveryOrderIl.getId());
        
        if(qty<=0){
            return true;
        }

        Query query = entityManager.createQuery("SELECT p FROM ProductInventoryRecord p WHERE p.product.productType =:p1");
        query.setParameter("p1", deliveryOrderIl.getProduct().getProductType());
        ProductInventoryRecord inventory = (ProductInventoryRecord) query.getResultList().get(0);
        LinkedList<FinishedGood> batches = new LinkedList<FinishedGood>(inventory.getBatches());
                       

        // sort batches into sortedBatches
        LinkedList<FinishedGood> sortedBatches = new LinkedList<FinishedGood>();
        for (int i = 0; i < batches.size(); i++) {
            int j;
            for (j = 0; j < sortedBatches.size(); j++) {
                if (sortedBatches.get(j).getDateManufactured().after(batches.get(i).getDateManufactured())) {
                    break;
                }
            }
            sortedBatches.add(j, batches.get(i));
        }
        System.out.println("Sorted Batch Size: " + sortedBatches.size());
        Long batchReservedQty;
        Long availableToReserveQty;
        List<InventoryReservation> reservations;
        deliveryOrderIl.setReservations(new ArrayList<InventoryReservation>());
        for (int i = 0; i < sortedBatches.size(); i++) {
            System.out.println("loop: i = " + i);
            reservations = sortedBatches.get(i).getReservations();
            batchReservedQty = getBatchReservedQty(reservations);
            availableToReserveQty = sortedBatches.get(i).getQuantity() - batchReservedQty;
            System.out.println("Batch Total Quantity: " + sortedBatches.get(i).getQuantity());
            System.out.println("Batch Reserved Qty: " + batchReservedQty);
            System.out.println("Available to reserve qty: " + availableToReserveQty);
            System.out.println("Quantity demanded: " + qty);
            if (availableToReserveQty > 0) {
                System.out.println("Inside availableToReserve > 0");
                InventoryReservation newReservation = new InventoryReservation();
                newReservation.setBatch(sortedBatches.get(i));
                newReservation.setDeliveryOrderItemLine(deliveryOrderIl);
                deliveryOrderIl.getReservations().add(newReservation);
                System.out.println("Added reservations");
                if (qty >= availableToReserveQty) {
                    newReservation.setReservationQty(availableToReserveQty);
                    qty = qty - availableToReserveQty;
                } else {
                    newReservation.setReservationQty(qty);
                    break;
                }
                entityManager.merge(newReservation);
                entityManager.flush();
                System.out.println("RESERVE INVENTORY: NEW reservation flushed.");
            }
        }

        //Update reserved Qty of inventory
        inventory.setReservedQty(inventory.getReservedQty() + toReserveQty);
        // TODO: Check return boolean
        return true;
    }

    private Long getBatchReservedQty(List<InventoryReservation> reservations) {
        Long reservedQty = 0L;
        for (int i = 0; i < reservations.size(); i++) {
            reservedQty = reservedQty + reservations.get(i).getReservationQty();
        }
        return reservedQty;
    }

    @Override
    public boolean unreserveInventory(Long deliveryOrderId) {
        try{
        Query query2 = entityManager.createQuery("SELECT d FROM DeliveryOrder d WHERE d.id =:p1");
        query2.setParameter("p1", deliveryOrderId);
        DeliveryOrder deliOrder = (DeliveryOrder) query2.getResultList().get(0);
        List<ItemLine> deliOrderIls = (List<ItemLine>) deliOrder.getItemLines();
        List<InventoryReservation> reservations;
        for (int i = 0; i < deliOrderIls.size(); i++) {
            reservations = deliOrderIls.get(i).getReservations();
            deliOrderIls.get(i).setReservations(null);
            Iterator reservationIterator = reservations.iterator();
            while(reservationIterator.hasNext()) {
                InventoryReservation ir = (InventoryReservation) reservationIterator.next();
                long toUnreserveQty = 0L;
                toUnreserveQty = toUnreserveQty + ir.getReservationQty();
                List<InventoryReservation> batchReservations = ir.getBatch().getReservations();
                batchReservations.remove(ir);
                ir.getBatch().setReservations(batchReservations);
                ProductInventoryRecord inventory = ir.getBatch().getInventory();
                inventory.setReservedQty(inventory.getReservedQty() - toUnreserveQty);
                ir.getDeliveryOrderItemLine().setReservations(null);
                ir.setDeliveryOrderItemLine(null);
                
                ir = entityManager.merge(ir);
                entityManager.flush();
                entityManager.remove(ir);
                entityManager.flush();
                
            }
//            for (int j = 0; j < reservations.size(); j++) {
//                long toUnreserveQty = 0L;
//                toUnreserveQty = toUnreserveQty + reservations.get(j).getReservationQty();
//                List<InventoryReservation> batchReservations = reservations.get(j).getBatch().getReservations();
//                batchReservations.remove(reservations.get(j));
//                reservations.get(j).getBatch().setReservations(batchReservations);
//                ProductInventoryRecord inventory = reservations.get(j).getBatch().getInventory();
//                inventory.setReservedQty(inventory.getReservedQty() + toUnreserveQty);
//
//                entityManager.remove(reservations.get(j));
//                entityManager.merge(reservations.get(j));
//            }
        }
        //TODO: check return boolean.
        return true;
        }
        catch (Exception e) {
            return false;
        }
    }

    @Override
    public FinishedGood arrangeBatchLocation(FinishedGood batch) {
//        Query query = entityManager.createQuery("SELECT f FROM FinishedGood f WHERE f.batchNo =:p1");
//        query.setParameter("p1", batchNo);
//        FinishedGood batch = (FinishedGood) query.getSingleResult();
        double batchQty = batch.getQuantity().doubleValue();
        String productType = batch.getInventory().getProduct().getProductType();
        WarehouseLocation wl;
        double storedQty;
        if (productType.contains("Classic")) {
            //put in storage 2
            mainloop:
            for (int j = 1; j < 10; j++) {
                // For each storage row
                for (int k = 1; k < 4; k++) {
                    // For each level
                    for (int l = 1; l < 101; l++) {
                        //100 bin on each level
                        String locationName = "S2R" + j + "L" + k + "B" + l;
                        Query query1 = entityManager.createQuery("SELECT w FROM WarehouseLocation w WHERE w.binLocation =:p1");
                        query1.setParameter("p1", locationName);
                        wl = (WarehouseLocation) query1.getResultList().get(0);
                        storedQty = wl.getStoredQty();
                        if (storedQty == 0) {
                            //empty to store a new batch
                            if (batchQty >= 100) {
                                wl.setStoredQty(100D);
                                batch.getLocations().add(wl);
                                wl.setBatch(batch);
                                batchQty = batchQty - 100;
                            } else {
                                wl.setStoredQty(batchQty);
                                batch.getLocations().add(wl);
                                wl.setBatch(batch);
                                batchQty = 0D;
                            }
                        }
                        if (batchQty == 0D) {
                            break mainloop;
                        }
                    }
                }
            }
        } else {
            //put in storage 3
            mainloop:
            for (int j = 1; j < 10; j++) {
                // For each storage row
                for (int k = 1; k < 4; k++) {
                    // For each level
                    for (int l = 1; l < 101; l++) {
                        //100 bin on each level
                        String locationName = "S3R" + j + "L" + k + "B" + l;
                        Query query2 = entityManager.createQuery("SELECT w FROM WarehouseLocation w WHERE w.binLocation =:p1");
                        query2.setParameter("p1", locationName);
                        wl = (WarehouseLocation) query2.getResultList().get(0);
                        storedQty = wl.getStoredQty();
                        if (storedQty == 0) {
                            //empty to store a new batch
                            if (batchQty >= 100) {
                                wl.setStoredQty(100D);
                                batch.getLocations().add(wl);
                                wl.setBatch(batch);
                                batchQty = batchQty - 100;
                            } else {
                                wl.setStoredQty(batchQty);
                                batch.getLocations().add(wl);
                                wl.setBatch(batch);
                                batchQty = 0D;
                            }
                        }
                        if (batchQty == 0D) {
                            break mainloop;
                        }

                    }
                }
            }
        }
        //Update inventory available Quantity
        Query query3 = entityManager.createQuery("SELECT p FROM ProductInventoryRecord p WHERE p.product.productType =:p1");
        query3.setParameter("p1", productType);
        ProductInventoryRecord inventoryRecord = (ProductInventoryRecord) query3.getSingleResult();
        inventoryRecord.setBalance(inventoryRecord.getBalance() + batch.getQuantity());
        inventoryRecord.getBatches().add(batch);

        return batch;

    }

    @Override
    public Long getRealInventory(String productType) {
        Query query = entityManager.createQuery("SELECT p FROM ProductInventoryRecord p WHERE p.product.productType =:p1");
        query.setParameter("p1", productType);
        ProductInventoryRecord productInventory = (ProductInventoryRecord) query.getSingleResult();
        return productInventory.getBalance() - productInventory.getReservedQty();
    }

    @Override
    public FinishedGood addBatchToInventoryRecord(FinishedGood fg, Product p) {

        Query q = entityManager.createQuery("SELECT r FROM ProductInventoryRecord r WHERE r.product.id =:p1");
        q.setParameter("p1", p.getId());

        ProductInventoryRecord r = (ProductInventoryRecord) q.getResultList().get(0);
        fg.setInventory(r);

        return fg;
    }

    @Override
    public Long getAvailableInventory(String productType) {
        Query query = entityManager.createQuery("SELECT p FROM ProductInventoryRecord p WHERE p.product.productType =:p1");
        query.setParameter("p1", productType);
        ProductInventoryRecord productInventory = (ProductInventoryRecord) query.getSingleResult();
        return productInventory.getBalance();
    }

    @Override
    public Collection<RawMaterialSalesOrder> getAllSalesOrderWOGoodsReceipt() {
        return entityManager.createQuery("SELECT s FROM RawMaterialSalesOrder s WHERE s.goodsReceipt IS NULL").getResultList();
    }

    @Override
    public Boolean createGoodsReceipt(GoodsReceipt gr) {

        gr.getSupplier().getReceipts().add(gr);
        // Save goods receipt
        if (gr.getRmso() != null) {
            gr.getRmso().setGoodsReceipt(gr);
            //calculate and set actual lead time to goodsreceipt
            Double oneDay = (double) (1 * 24 * 60 * 60 * 1000);
            Double actualLeadTime = ((double) gr.getDateReceipt().getTime() - (double) gr.getRmso().getRmpo().getPoDate().getTime()) / oneDay;
            gr.setActualLeadTime(actualLeadTime);
            
            //Update supplier average actual lead time
            ArrayList<GoodsReceipt> grList = new ArrayList<GoodsReceipt>(gr.getSupplier().getReceipts());
            Double totalLeadTime = 0.0;
            for (int i = 0; i < grList.size(); i++) {
                totalLeadTime += grList.get(i).getActualLeadTime();
            }
            if(grList.isEmpty()){
                gr.getSupplier().setActualLeadTime(0.0d);
            } else {
                gr.getSupplier().setActualLeadTime(totalLeadTime / (double) grList.size());
            }
        }
        gr = entityManager.merge(gr);
        
        ArrayList<RawMaterialInventoryBatch> rmibs = new ArrayList<RawMaterialInventoryBatch>();

        // Assume 1 case = 10 kg
        // Hence, raw material A with quantity 10 kg, occupies 1 capacity in a bin.
        // A bin with 100 capacity can hold 1000 kg of raw material A.
        for (RawMaterialItemLine rmil : gr.getItemlines()) {

            RawMaterialInventoryBatch rmib = new RawMaterialInventoryBatch();
            rmib.setBatchNo(generateRawMaterialBatchNumber(rmil.getRawMaterialSupplyInformation().getRawMaterial()));
            Timestamp today = new Timestamp(System.currentTimeMillis());
            long oneDay = (long) (1 * 24 * 60 * 60 * 1000);
            rmib.setDateOrdered(today);
            Timestamp expire = new Timestamp(today.getTime());
            for(int i = 0; i < rmil.getRawMaterialSupplyInformation().getRawMaterial().getShelfLife().intValue()*30; i++){
                expire.setTime(expire.getTime() + oneDay);
            }
            System.out.println("Time stamp: " + expire.toString());
            System.out.println(oneDay + "." + rmil.getRawMaterialSupplyInformation().getRawMaterial().getShelfLife());
            rmib.setDateExpired(expire);
            RawMaterialInventoryRecord rec = getRawMaterialInventoryRecordByRawMaterial(rmil.getRawMaterialSupplyInformation().getRawMaterial());
            rmib.setInventory(rec);
            rmib.setGoodsReceipt(gr);
            rmib.setLocations(new ArrayList<WarehouseLocation>());
            rmib.setQuantity(((Long)(rmil.getQty()*rmil.getRawMaterialSupplyInformation().getRawMaterial().getLotSize())).doubleValue() / (double) 10);
            rmib.setQuantityAvailable(((Long)(rmil.getQty()*rmil.getRawMaterialSupplyInformation().getRawMaterial().getLotSize())).doubleValue()/ (double) 10);

            rec.setBalance((double) rec.getBalance() + (double) rmib.getQuantity());
            rec.getBatches().add(rmib);

            rmib = entityManager.merge(rmib);
            rec = entityManager.merge(rec);
            rmibs.add(rmib);
        }
        
        entityManager.flush();
        gr = (GoodsReceipt)entityManager.createQuery("SELECT gr FROM GoodsReceipt gr WHERE gr.goodsReceiptId LIKE '" + gr.getGoodsReceiptId() +"'").getResultList().get(0);

        // Allocate to warehouse location

        for (RawMaterialInventoryBatch rmib : rmibs) {
            System.out.println("Add Raw Material Batch");
            double rmibQty = rmib.getQuantity();
            WarehouseLocation wl;
            double storedQty;
            //put in storage 1
            mainloop:
            for (int j = 1; j < 10; j++) {
                // For each storage row
                for (int k = 1; k < 4; k++) {
                    // For each level
                    for (int l = 1; l < 101; l++) {
                        //100 bin on each level
                        String locationName = "S1R" + j + "L" + k + "B" + l;
                        Query query1 = entityManager.createQuery("SELECT w FROM WarehouseLocation w WHERE w.binLocation =:p1");
                        query1.setParameter("p1", locationName);
                        wl = (WarehouseLocation) query1.getResultList().get(0);
                        storedQty = wl.getStoredQty();
                        if (storedQty == 0) {
                            //empty to store a new rmib
                            if (rmibQty >= 100) {
                                wl.setStoredQty(100D);
                                rmib.getLocations().add(wl);
                                wl.setRmBatch(rmib);
                                rmibQty = rmibQty - 100;
                            } else {
                                wl.setStoredQty(rmibQty);
                                rmib.getLocations().add(wl);
                                wl.setRmBatch(rmib);
                                rmibQty = 0D;
                            }
                        }
                        if (rmibQty == 0D) {
                            break mainloop;
                        }
                    }
                }
            }

        }

        entityManager.merge(gr);
        entityManager.flush();
        return true;
    }

    @Override
    public String generateRawMaterialBatchNumber(RawMaterial r) {

        Query q = entityManager.createQuery("SELECT b FROM RawMaterialInventoryBatch b WHERE b.inventory.rawMaterial.name LIKE :p1");
        q.setParameter("p1", r.getName());

        Collection<RawMaterialInventoryBatch> list = q.getResultList();
        int size = q.getResultList().size() + 1;
        String backId = String.format("%04d", size);
        String batchNo = r.getAcronym() + backId;

        return batchNo;
    }

    @Override
    public Boolean consumeRawMaterialInventory(List<Double> consumed) {

        ArrayList<RawMaterialInventoryRecord> recList = new ArrayList<RawMaterialInventoryRecord>();
        for (String s : Consts.allRawMaterialNames) {
            Query q = entityManager.createQuery("SELECT r FROM RawMaterialInventoryRecord r WHERE r.rawMaterial.name =:p1");
            q.setParameter("p1", s);
            RawMaterialInventoryRecord rec = (RawMaterialInventoryRecord) q.getResultList().get(0);
            recList.add(rec);
        }

        // Check sufficient quantity
        int a = 0;
        for (RawMaterialInventoryRecord rec : recList) {
            if (rec.getBalance().doubleValue() < consumed.get(a)) {
                return false;
            }
            a++;
        }

        int i = 0;

        for (RawMaterialInventoryRecord rec : recList) {

            double materialUsed = consumed.get(i);
            batchloop:
            for (RawMaterialInventoryBatch batch : rec.getBatches()) {

                double quantityReduced = 0d;
                ArrayList<WarehouseLocation> emptyLocations = new ArrayList<WarehouseLocation>();
                warehouseloop:
                for (WarehouseLocation w : batch.getLocations()) {
                    double balance = w.getStoredQty();
                    if (balance >= materialUsed) {
                        w.setStoredQty(balance - materialUsed);
                        quantityReduced += materialUsed;
                        materialUsed = 0d;
                    }
                    if (balance < materialUsed) {
                        materialUsed = materialUsed - balance;
                        quantityReduced += balance;
                        w.setStoredQty(0D);
                        emptyLocations.add(w);
                    }
                    if (materialUsed == 0d) {
                        break warehouseloop;
                    }
                }
                for (WarehouseLocation w : emptyLocations) {
                    w.setRmBatch(null);
                    batch.getLocations().remove(w);
                }
                batch.setQuantityAvailable(batch.getQuantityAvailable().doubleValue() - quantityReduced);
                if (materialUsed == 0d) {
                    break batchloop;
                }
            }
            rec.setBalance(rec.getBalance().doubleValue() - consumed.get(i).doubleValue());
            entityManager.merge(rec);
            i++;
        }
        return true;
    }

    @Override
    public Boolean matchGoodsReceiptItems(GoodsReceipt gr) {

        ArrayList<RawMaterialItemLine> grItems = new ArrayList<RawMaterialItemLine>(gr.getItemlines());
        ArrayList<RawMaterialItemLine> soItems = new ArrayList<RawMaterialItemLine>(gr.getRmso().getItemlines());

        if (soItems.size() != grItems.size()) {
            return false;
        }

        for (int i = 0; i < soItems.size(); i++) {

            RawMaterialItemLine soil = soItems.get(i);
            boolean found = false;
            for (int k = 0; k < grItems.size(); k++) {
                RawMaterialItemLine gril = grItems.get(k);
                if (soil.getRawMaterialSupplyInformation().getRawMaterial().getName().equals(gril.getRawMaterialSupplyInformation().getRawMaterial().getName())
                        && (long) soil.getQty() == (long) gril.getQty()) {
                    found = true;
                }
                if (found) {
                    soItems.remove(i);
                    grItems.remove(k);
                    i = -1;
                    break;
                }
            }
        }

        if (grItems.isEmpty() && soItems.isEmpty()) {
            return true;
        }
        return false;
    }

    @Override
    public GoodsReceipt getGoodsReceiptById(Long id) {
        return (GoodsReceipt) entityManager.createQuery("SELECT gr FROM GoodsReceipt gr WHERE gr.id = " + id).getResultList().get(0);
    }

    @Override
    public GoodsReceipt updateGoodsReceipt(GoodsReceipt gr) {
        return entityManager.merge(gr);
    }

    @Override
    public String generateGoodsReceiptId() {
        //Get date YYYYMMDD
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String frontId = sdf.format(cal.getTime());

        //Query SalesOrder table, anything like 'yyMMdd'
        Query query = entityManager.createQuery("SELECT gr FROM GoodsReceipt gr WHERE gr.goodsReceiptId LIKE :p1");
        query.setParameter("p1", "%" + frontId + "%");

        //Get result size
        int size = query.getResultList().size() + 1;
        String backId = String.format("%04d", size);

        //Set new ID to 'YYYYMMDD' + 'size + 1'
        String id = "GR" + frontId + "/" + backId;
        //return id
        return id;
    }
}
